import datetime
import os.path
import sys

import numpy as np
import pandas as pd
from common import common


class Getdata:
    def __init__(self, zd):
        self.zd = zd
        self.flag = False  # 标记是否获取数据成功

    def getds(self):
        df = self.data1()  # 获取任务令表数据
        df1 = self.data2()  # 获取子库基表数据
        if self.flag:
            return None

        df = df.merge(df1, left_on='ICARE发料子库', right_on='SECONDARY_INVENTORY_NAME', how='left')
        df.loc[df['责任库房'] == '中国区坏件', '任务令'] = 'abcd'
        conditions = [
            (df['任务令'] == 'abcd'),
            (df['任务令'].str.startswith('YR', na=False) | df['任务令'].str.startswith('YJ', na=False)),
            (df['任务令'].str.startswith('RC', na=False)),
            (df['ICARE发料子库'].str.startswith('HQ0D2', na=False)),
            (df['ICARE发料子库'].str.startswith('HQ0D1', na=False)),
            (df['需求方*'] == '中国区企业网')
        ]
        values = ['中国区坏件', '中国区原件', '海外原件', '保税坏件', '央库坏件', '企业网坏件']
        df['责任库房'] = np.select(conditions, values, default='其他')

        df["类型"] = ['无线模块' if '无线模块' in v else '单板与其他' for v in df['维修车间']]
        df = df[df['责任库房'] != '其他']
        dfs = df.pivot_table(index=['责任库房', '类型'], values='待下单量', aggfunc='sum').reset_index()

        path = os.path.join(os.path.dirname(os.path.realpath(sys.argv[0])), '结果')
        common.create_folder_if_not_exists(path)
        spath = os.path.join(path, f'数据汇总表{datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S')}.xlsx')
        dfs.to_excel(spath, index=False)
        common.show_message(f'数据汇总表已保存到{spath}', 1)



    # 获取原表数据
    def data1(self):
        df = pd.read_excel(self.zd['z1'], sheet_name='任务令',
                           usecols=['任务令', 'ICARE发料子库', '待下单量', '维修车间', '需求方*'])
        if df.empty:
            self.flag = True
            common.show_message('没有获取到任务令表数据', 0)
            return None
        return df
    # 获取月数据汇总表数据
    def data2(self):
        df = pd.read_excel(self.zd['z2'], sheet_name='子库基表',
                            usecols=['SECONDARY_INVENTORY_NAME', '责任库房'])
        if df.empty:
            self.flag = True
            common.show_message('没有获取到子库基表数据', 0)
            return None
        return df